package com.affablebean.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.affablebean.model.entity.Category;
import com.affablebean.model.entity.Product;

public class ProductDAL extends AbstractDAL<Product> {

	public ProductDAL() {
		super("PRODUCTS");
	}

	@Override
	public boolean insertEntity(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "INSERT INTO products VALUES (product_seq.NEXTVAL, ?, ?, ?, ?, ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd, new String[] { "product_id" })) {
				statement.setString(1, entity.getName());
				statement.setDouble(2, entity.getPrice());
				statement.setString(3, entity.getDescription());
				statement.setDate(4, convertToSqlDate(entity.getLastUpdate()));
				statement.setInt(5, entity.getCategory().getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
						if (generatedKeys.next()) {
							entity.setId(generatedKeys.getInt(1));
						}
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean updateEntity(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "UPDATE products SET product_name = ?, price = ?, description = ?, last_update = ?, category_id = ? WHERE product_id = ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, entity.getName());
				statement.setDouble(2, entity.getPrice());
				statement.setString(3, entity.getDescription());
				statement.setDate(4, convertToSqlDate(entity.getLastUpdate()));
				statement.setInt(5, entity.getCategory().getId());
				statement.setInt(6, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean deleteEntity(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "DELETE FROM products WHERE product_id = ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public List<Product> getAll() throws SQLException {
		List<Product> products = new ArrayList<Product>();
		try (Connection connection = createNewConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT p.product_id,p.product_name,p.price price,p.description,p.last_update,c.category_id,c.category_name FROM products p INNER JOIN categories c ON c.category_id = p.category_id";
				try (ResultSet resultSet = statement.executeQuery(sqlCmd)) {
					while (resultSet.next()) {
						products.add(createEntity(resultSet));
					}
				}
			}
		}
		return products;
	}

	@Override
	public Product findById(int id) throws SQLException {
		Product product = null;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "SELECT p.product_id,p.product_name,p.price price,p.description,p.last_update,c.category_id,c.category_name FROM products p INNER JOIN categories c ON c.category_id = p.category_id WHERE product_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery()) {
					if (resultSet.next()) {
						product = createEntity(resultSet);
					}
				}
			}
		}

		return product;
	}

	@Override
	Product createEntity(ResultSet resultSet) throws SQLException {
		Product product = new Product();
		product.setId(resultSet.getInt("product_id"));
		product.setName(resultSet.getString("product_name"));
		product.setDescription(resultSet.getString("description"));
		product.setLastUpdate(resultSet.getDate("last_update"));
		{
			Category category = new Category();
			category.setId(resultSet.getInt("category_id"));
			category.setName(resultSet.getString("category_name"));
			product.setCategory(category);
		}
		return product;
	}

}
